package com.campusbackend.mapper;

import com.campusbackend.entity.Teams;
import com.campusbackend.entity.vo.TeamDetailVO;
import com.campusbackend.entity.vo.TeamListVO;
import com.campusbackend.entity.vo.TeamEditVO;
import com.campusbackend.entity.vo.StudentTeamVO;
import com.campusbackend.entity.vo.AdminTeamListVO;
import com.campusbackend.entity.vo.AdminTeamDetailVO;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Param;

import java.util.List;

/**
 * <p>
 * 队伍表 Mapper 接口
 * </p>
 *
 * @author yezi
 * @since 2025-07-26
 */
@Mapper
public interface TeamsMapper extends BaseMapper<Teams> {

    /**
     * 获取队伍成员详细信息
     * @param teamId 队伍ID
     * @return 成员信息列表
     */
    @Select("SELECT " +
            "tm.student_id, " +
            "s.student_id as student_number, " +
            "s.real_name, " +
            "s.avatar, " +
            "s.college, " +
            "tm.member_role, " +
            "tm.score, " +
            "tm.join_time " +
            "FROM team_members tm " +
            "LEFT JOIN students s ON tm.student_id = s.id " +
            "WHERE tm.team_id = #{teamId} AND tm.join_status = 1 " +
            "ORDER BY " +
            "CASE tm.member_role " +
            "WHEN '队长' THEN 1 " +
            "WHEN '副队长' THEN 2 " +
            "WHEN '技术负责人' THEN 3 " +
            "WHEN '项目经理' THEN 4 " +
            "ELSE 5 END")
    List<TeamDetailVO.MemberInfo> getTeamMembers(@Param("teamId") Long teamId);

    /**
     * 获取指导老师信息
     * @param instructorId 指导老师ID
     * @return 指导老师信息
     */
    @Select("SELECT " +
            "id as teacher_id, " +
            "real_name, " +
            "avatar, " +
            "professional_title, " +
            "email, " +
            "phone " +
            "FROM teachers " +
            "WHERE id = #{instructorId}")
    TeamDetailVO.InstructorInfo getInstructorInfo(@Param("instructorId") Long instructorId);

    /**
     * 获取入驻专家信息（排除指导老师）
     * @param teamId 队伍ID
     * @param instructorId 指导老师ID（需要排除）
     * @return 专家信息列表
     */
    @Select("SELECT " +
            "te.expert_id, " +
            "t.real_name, " +
            "t.avatar, " +
            "t.professional_title, " +
            "t.email, " +
            "te.join_status, " +
            "te.join_time " +
            "FROM team_experts te " +
            "LEFT JOIN teachers t ON te.expert_id = t.id " +
            "WHERE te.team_id = #{teamId} AND te.expert_id != #{instructorId} " +
            "ORDER BY te.join_time DESC")
    List<TeamDetailVO.ExpertInfo> getTeamExperts(@Param("teamId") Long teamId, @Param("instructorId") Long instructorId);

    /**
     * 获取教师组建的队伍列表
     * @param instructorId 指导老师ID
     * @return 组建的队伍列表
     */
    @Select("SELECT " +
            "t.id as team_id, " +
            "t.team_name, " +
            "t.team_code, " +
            "t.team_type, " +
            "t.instructor_id, " +
            "te.real_name as instructor_name, " +
            "t.current_members, " +
            "t.max_members, " +
            "t.status, " +
            "t.create_time, " +
            "'instructor' as role_in_team " +
            "FROM teams t " +
            "LEFT JOIN teachers te ON t.instructor_id = te.id " +
            "WHERE t.instructor_id = #{instructorId} AND t.status = 1 " +
            "ORDER BY t.create_time DESC")
    List<TeamListVO> getTeacherCreatedTeams(@Param("instructorId") Long instructorId);

    /**
     * 获取教师入驻的队伍列表
     * @param expertId 专家ID（教师ID）
     * @return 入驻的队伍列表
     */
    @Select("SELECT " +
            "t.id as team_id, " +
            "t.team_name, " +
            "t.team_code, " +
            "t.team_type, " +
            "t.instructor_id, " +
            "te.real_name as instructor_name, " +
            "t.current_members, " +
            "t.max_members, " +
            "t.status, " +
            "t.create_time, " +
            "'expert' as role_in_team " +
            "FROM teams t " +
            "LEFT JOIN teachers te ON t.instructor_id = te.id " +
            "LEFT JOIN team_experts tex ON t.id = tex.team_id " +
            "WHERE tex.expert_id = #{expertId} AND tex.join_status = 1 AND t.status = 1 " +
            "ORDER BY tex.join_time DESC")
    List<TeamListVO> getTeacherExpertTeams(@Param("expertId") Long expertId);

    /**
     * 获取所有队伍列表（根据当前教师ID判断角色关系）
     * @param currentTeacherId 当前登录教师ID
     * @return 所有队伍列表
     */
    @Select("SELECT " +
            "t.id as team_id, " +
            "t.team_name, " +
            "t.team_code, " +
            "t.team_type, " +
            "t.instructor_id, " +
            "te.real_name as instructor_name, " +
            "t.current_members, " +
            "t.max_members, " +
            "t.status, " +
            "t.create_time, " +
            "CASE " +
            "  WHEN t.instructor_id = #{currentTeacherId} THEN 'instructor' " +
            "  WHEN tex.expert_id = #{currentTeacherId} AND tex.join_status = 1 THEN 'expert' " +
            "  ELSE '' " +
            "END as role_in_team " +
            "FROM teams t " +
            "LEFT JOIN teachers te ON t.instructor_id = te.id " +
            "LEFT JOIN team_experts tex ON t.id = tex.team_id AND tex.expert_id = #{currentTeacherId} " +
            "WHERE t.status = 1 " +
            "ORDER BY t.create_time DESC")
    List<TeamListVO> getAllTeams(@Param("currentTeacherId") Long currentTeacherId);

    /**
     * 获取队伍编辑信息的成员列表
     * @param teamId 队伍ID
     * @return 成员编辑信息列表
     */
    @Select("SELECT " +
            "tm.student_id, " +
            "s.real_name, " +
            "s.student_id as student_number, " +
            "s.college, " +
            "tm.member_role, " +
            "tm.join_time " +
            "FROM team_members tm " +
            "LEFT JOIN students s ON tm.student_id = s.id " +
            "WHERE tm.team_id = #{teamId} AND tm.join_status = 1 " +
            "ORDER BY " +
            "CASE tm.member_role " +
            "WHEN '队长' THEN 1 " +
            "WHEN '副队长' THEN 2 " +
            "WHEN '技术负责人' THEN 3 " +
            "WHEN '项目经理' THEN 4 " +
            "ELSE 5 END")
    List<TeamEditVO.TeamMemberEditVO> getTeamEditMembers(@Param("teamId") Long teamId);

    /**
     * 根据队伍编号或名称搜索队伍
     * @param keyword 搜索关键词（队伍编号或名称）
     * @param currentTeacherId 当前教师ID（用于判断角色关系）
     * @return 匹配的队伍列表
     */
    @Select("SELECT " +
            "t.id as team_id, " +
            "t.team_name, " +
            "t.team_code, " +
            "t.team_type, " +
            "t.instructor_id, " +
            "te.real_name as instructor_name, " +
            "t.current_members, " +
            "t.max_members, " +
            "t.status, " +
            "t.create_time, " +
            "CASE " +
            "  WHEN t.instructor_id = #{currentTeacherId} THEN 'instructor' " +
            "  WHEN tex.expert_id = #{currentTeacherId} AND tex.join_status = 1 THEN 'expert' " +
            "  ELSE '' " +
            "END as role_in_team " +
            "FROM teams t " +
            "LEFT JOIN teachers te ON t.instructor_id = te.id " +
            "LEFT JOIN team_experts tex ON t.id = tex.team_id AND tex.expert_id = #{currentTeacherId} " +
            "WHERE t.status = 1 " +
            "AND (t.team_code LIKE CONCAT('%', #{keyword}, '%') OR t.team_name LIKE CONCAT('%', #{keyword}, '%')) " +
            "ORDER BY t.create_time DESC")
    List<TeamListVO> searchTeams(@Param("keyword") String keyword, @Param("currentTeacherId") Long currentTeacherId);

    /**
     * 获取学生所属的队伍信息
     * @param studentId 学生ID
     * @return 学生队伍信息
     */
    @Select("SELECT " +
            "tm.team_id, " +
            "tm.member_role, " +
            "tm.score, " +
            "tm.join_time, " +
            "t.team_name, " +
            "t.team_code, " +
            "t.team_type, " +
            "t.team_intro, " +
            "t.current_competition, " +
            "t.award_info, " +
            "t.current_members, " +
            "t.max_members, " +
            "t.status, " +
            "t.create_time, " +
            "t.instructor_id " +
            "FROM team_members tm " +
            "LEFT JOIN teams t ON tm.team_id = t.id " +
            "WHERE tm.student_id = #{studentId} AND tm.join_status = 1 AND t.status = 1")
    StudentTeamVO.TeamInfo getStudentTeamInfo(@Param("studentId") Long studentId);

    /**
     * 获取学生基本信息
     * @param studentId 学生ID
     * @return 学生基本信息
     */
    @Select("SELECT " +
            "id as student_id, " +
            "student_id as student_number, " +
            "real_name as student_name, " +
            "college " +
            "FROM students " +
            "WHERE id = #{studentId}")
    StudentTeamVO getStudentBasicInfo(@Param("studentId") Long studentId);

    /**
     * 获取指导老师信息（用于学生队伍查询）
     * @param instructorId 指导老师ID
     * @return 指导老师信息
     */
    @Select("SELECT " +
            "id as teacher_id, " +
            "employee_id, " +
            "real_name, " +
            "professional_title, " +
            "email, " +
            "phone, " +
            "avatar " +
            "FROM teachers " +
            "WHERE id = #{instructorId}")
    StudentTeamVO.InstructorInfo getInstructorInfoForStudent(@Param("instructorId") Long instructorId);

    /**
     * 获取队伍成员信息（用于学生队伍查询）
     * @param teamId 队伍ID
     * @return 成员信息列表
     */
    @Select("SELECT " +
            "tm.student_id, " +
            "s.student_id as student_number, " +
            "s.real_name, " +
            "s.college, " +
            "s.avatar, " +
            "tm.member_role, " +
            "tm.score, " +
            "tm.join_time " +
            "FROM team_members tm " +
            "LEFT JOIN students s ON tm.student_id = s.id " +
            "WHERE tm.team_id = #{teamId} AND tm.join_status = 1 " +
            "ORDER BY " +
            "CASE tm.member_role " +
            "WHEN '队长' THEN 1 " +
            "WHEN '副队长' THEN 2 " +
            "WHEN '技术负责人' THEN 3 " +
            "WHEN '项目经理' THEN 4 " +
            "ELSE 5 END")
    List<StudentTeamVO.MemberInfo> getTeamMembersForStudent(@Param("teamId") Long teamId);

    /**
     * 获取入驻专家信息（用于学生队伍查询，排除指导老师）
     * @param teamId 队伍ID
     * @param instructorId 指导老师ID（需要排除）
     * @return 专家信息列表
     */
    @Select("SELECT " +
            "te.expert_id, " +
            "t.employee_id, " +
            "t.real_name, " +
            "t.professional_title, " +
            "t.email, " +
            "t.phone, " +
            "t.avatar, " +
            "te.join_time " +
            "FROM team_experts te " +
            "LEFT JOIN teachers t ON te.expert_id = t.id " +
            "WHERE te.team_id = #{teamId} AND te.expert_id != #{instructorId} AND te.join_status = 1 " +
            "ORDER BY te.join_time DESC")
    List<StudentTeamVO.ExpertInfo> getTeamExpertsForStudent(@Param("teamId") Long teamId, @Param("instructorId") Long instructorId);

    /* ========== 管理端查询方法 ========== */

    /**
     * 管理端 - 获取所有队伍列表
     * @return 所有队伍列表
     */
    @Select("SELECT " +
            "t.id as team_id, " +
            "t.team_name, " +
            "t.team_code, " +
            "t.team_type, " +
            "t.instructor_id, " +
            "te.real_name as instructor_name, " +
            "te.employee_id as instructor_employee_id, " +
            "t.current_members, " +
            "t.max_members, " +
            "t.status, " +
            "t.create_time, " +
            "t.update_time, " +
            "(SELECT COUNT(*) FROM team_experts tex WHERE tex.team_id = t.id AND tex.join_status = 1) as expert_count " +
            "FROM teams t " +
            "LEFT JOIN teachers te ON t.instructor_id = te.id " +
            "WHERE t.status = 1 " +
            "ORDER BY t.create_time DESC")
    List<AdminTeamListVO> getAdminTeamList();

    /**
     * 管理端 - 根据队伍名称或编号搜索队伍
     * @param keyword 搜索关键词
     * @return 匹配的队伍列表
     */
    @Select("SELECT " +
            "t.id as team_id, " +
            "t.team_name, " +
            "t.team_code, " +
            "t.team_type, " +
            "t.instructor_id, " +
            "te.real_name as instructor_name, " +
            "te.employee_id as instructor_employee_id, " +
            "t.current_members, " +
            "t.max_members, " +
            "t.status, " +
            "t.create_time, " +
            "t.update_time, " +
            "(SELECT COUNT(*) FROM team_experts tex WHERE tex.team_id = t.id AND tex.join_status = 1) as expert_count " +
            "FROM teams t " +
            "LEFT JOIN teachers te ON t.instructor_id = te.id " +
            "WHERE t.status = 1 " +
            "AND (t.team_name LIKE CONCAT('%', #{keyword}, '%') OR t.team_code LIKE CONCAT('%', #{keyword}, '%')) " +
            "ORDER BY t.create_time DESC")
    List<AdminTeamListVO> adminSearchTeams(@Param("keyword") String keyword);

    /**
     * 管理端 - 获取指导老师详细信息
     * @param instructorId 指导老师ID
     * @return 指导老师信息
     */
    @Select("SELECT " +
            "id as teacher_id, " +
            "employee_id, " +
            "real_name, " +
            "avatar, " +
            "professional_title, " +
            "email, " +
            "phone, " +
            "audit_status " +
            "FROM teachers " +
            "WHERE id = #{instructorId}")
    AdminTeamDetailVO.InstructorInfo getAdminInstructorInfo(@Param("instructorId") Long instructorId);

    /**
     * 管理端 - 获取队伍成员详细信息
     * @param teamId 队伍ID
     * @return 成员信息列表
     */
    @Select("SELECT " +
            "tm.student_id, " +
            "s.student_id as student_number, " +
            "s.real_name, " +
            "s.avatar, " +
            "s.college, " +
            "s.audit_status, " +
            "tm.member_role, " +
            "tm.score, " +
            "tm.join_time " +
            "FROM team_members tm " +
            "LEFT JOIN students s ON tm.student_id = s.id " +
            "WHERE tm.team_id = #{teamId} AND tm.join_status = 1 " +
            "ORDER BY " +
            "CASE tm.member_role " +
            "WHEN '队长' THEN 1 " +
            "WHEN '副队长' THEN 2 " +
            "WHEN '技术负责人' THEN 3 " +
            "WHEN '项目经理' THEN 4 " +
            "ELSE 5 END")
    List<AdminTeamDetailVO.MemberInfo> getAdminTeamMembers(@Param("teamId") Long teamId);

    /**
     * 管理端 - 获取入驻专家信息（排除指导老师）
     * @param teamId 队伍ID
     * @param instructorId 指导老师ID（需要排除）
     * @return 专家信息列表
     */
    @Select("SELECT " +
            "te.expert_id, " +
            "t.employee_id, " +
            "t.real_name, " +
            "t.avatar, " +
            "t.professional_title, " +
            "t.email, " +
            "t.phone, " +
            "te.join_status, " +
            "te.join_time " +
            "FROM team_experts te " +
            "LEFT JOIN teachers t ON te.expert_id = t.id " +
            "WHERE te.team_id = #{teamId} AND te.expert_id != #{instructorId} " +
            "ORDER BY te.join_time DESC")
    List<AdminTeamDetailVO.ExpertInfo> getAdminTeamExperts(@Param("teamId") Long teamId, @Param("instructorId") Long instructorId);
}
